package macroExcel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;




import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ManiputlateMacroXlsm {

	public  static void main(String args[]){
		
		//查找该目录下的ppt/pptx
		String url = "src/file";
		//不包括改文件名
		String notIncludeFile = "2016年10月各专业公司经营点评";
		//文件名的list
		List<String> list = GetNameOfPPT(url,notIncludeFile);
		//把文件名写入url_xls
		String url_xls = "src/file/test.xlsm";
		//写入xlsm函数
		writer(url_xls,list,2,6);
	}
			
		
		
	public static void writer(String uri,List<String> list,int indexRow,int indexCol)  {  
		
	        Workbook wb = null; 
	        //源文件的copy的路径
	        String excelPath = uri.substring(0, uri.indexOf(".xlsm")) + "_copy" + ".xlsm";
	        File file = new File(excelPath);
	        Sheet sheet =null;
	        //创建工作文档对象   
	        try{
	        	
	        	if (!file.exists()) {
	        		wb = new XSSFWorkbook(
	        				OPCPackage.open(uri)
	        				);
	        		//创建sheet对象   
	        		sheet = (Sheet) wb.getSheetAt(0);
	        		OutputStream outputStream = new FileOutputStream(excelPath);
	        		wb.write(outputStream);
	        		outputStream.flush();
	        		outputStream.close();
	        		
	        	} else {
	        		wb = new XSSFWorkbook(
	        				OPCPackage.open(uri)
	        				);
	        	}
	        }catch(Exception e){
	        	
	        }
	         //创建sheet对象   
	        if (sheet==null) {
	            sheet = (Sheet) wb.getSheetAt(0);
	        }
	        
	        Row row = null;
	        Cell cell = null;
	        for(int i = 0;i < list.size();i++){
	        	
	        	row = sheet.getRow(indexRow + i);
	        	if(row == null) row = sheet.createRow(indexRow + i);
	        	cell = row.getCell(indexCol);
	        	if(cell == null) cell = row.createCell(indexCol);
	        	cell.setCellValue(list.get(i));
	        }
	        
	        row = sheet.getRow(1);
	        cell = row.createCell(7);
	        cell.setCellValue(list.size());
	        try{
	        	
	        	//创建文件流   
	        	OutputStream stream = new FileOutputStream(excelPath);  
	        	//写入数据   
	        	wb.write(stream);  
	        	//关闭文件流   
	        	stream.close();  
	        	wb.close();
	        	
	        	
	        }catch(Exception e){
	        	e.printStackTrace();
	        }
	        //删除源文件，copy文件改名为源文件
	        File copy = new File(excelPath);
	        if(copy.exists()){
	        	deleteFile(uri);
	        	copy.renameTo(new File(uri));
	        }
	        System.err.println("包含宏的excel已经创建！");
	    }  
	
	
	/**
	 * Description:    删除文件
	 * @param sPath	        要删除的文件路径
	 * 
	 * Modification History:  4.11日16:12分
	 * Date        Version       Author          Description  
	 * ------------------------------------------------------------------  
	 * 2017年4月11日    compress     ZHANGQI975     创建
	 */
	public static void deleteFile(String sPath) {  
	    File file = new File(sPath);  
	    // 路径为文件且不为空则进行删除  
	    if (file.isFile() && file.exists()) {  
	        file.delete();  
	    }  
	    return; 
	}  
	
    /**
     * Description:    输入目录名，返回name列表
     * @param sourceFilePath	目录名
     * @param notIncludeFile	不包括的文件名
     * @return					一个list的文件名，类型为string
     * 
     * Modification History:  
     * Date        Version       Author          Description  
     * ------------------------------------------------------------------  
     * 2017年4月11日    compress     ZHANGQI975     创建
     */
    public static List<String> GetNameOfPPT(String sourceFilePath,String notIncludeFile){  
    	
    	List<String> nameList = new ArrayList<String>();
        try {  

        	File sourceFile = new File(sourceFilePath);
        	File[] sourceFiles = sourceFile.listFiles();  

        	for(int i=0;i<sourceFiles.length;i++){ 
        		
        		String name = sourceFiles[i].getName();
        		
        		if(name.endsWith(".ppt")||name.endsWith(".pptx")||name.endsWith("PPT")||name.endsWith("PPTX")){
        			
        			String fileName = name.substring(0, name.indexOf("."));
        			//文件名不等于那个特殊的，则加入
        			if(!fileName.equals(notIncludeFile)){
        				
        				nameList.add(name.substring(0, name.indexOf(".")));
        			}
        		}
        	}

        } catch (Exception e) {  
        	
        	System.out.println("error");
            e.printStackTrace();  
        } finally{
           
        } 
        return nameList;
    } 
  }
